import sys,os.path,os,csv
from openpyxl import load_workbook
from openpyxl.utils import get_column_letter
from openpyxl.utils import column_index_from_string
from typing import Dict, Optional, List, Any, Union
from PySide6.QtGui import *
from PySide6.QtWidgets import *
from PySide6.QtCore import *
from datetime import datetime
import win32com.client as win32
from win32com.client import constants as xl
import pythoncom
import xlwings as xw
from PIL import ImageGrab
from io import BytesIO

class OpenFileWidget(QWidget):
    def __init__(self, title: str):
        super().__init__()
        self.label = QLabel(title)
        self.edit = QLineEdit()
        self.button_open = QPushButton('open file')
        self.layout = QHBoxLayout(self)
        self.layout.addWidget(self.label)
        self.layout.addWidget(self.edit)
        self.layout.addWidget(self.button_open)
        self.edit.setReadOnly(True)
        
        self.button_open.clicked.connect(self.ChooseExcelFile)

    def ChooseExcelFile(self): # open excel
        file_path = QFileDialog.getOpenFileName(self)
        self.edit.setText(file_path[0])
        # 选择目录
        # directory = QFileDialog.getExistingDirectory(self, "选择目录")  

    def clear(self):
        """清除已选择的文件路径"""
        self.edit.clear()

class LineTextWidget(QWidget):
    def __init__(self, title: str, max_length: int = None, display_width: int = None, text: str = None):
        super().__init__()
        self.label = QLabel(title)
        self.line_text = QLineEdit()
        # # 精确计算label所需最小宽度（关键步骤）
        # text_width = self.label.fontMetrics().boundingRect(title).width()
        # self.label.setFixedWidth(text_width + 2)  # +2px防止字体差异导致的截断
        # # 设置控件属性
        # self.label.setAlignment(Qt.AlignLeft | Qt.AlignVCenter)
        # self.label.setSizePolicy(QSizePolicy.Fixed, QSizePolicy.Preferred)
        # 设置最大输入长度（可选）
        if max_length is not None:
            self.line_text.setMaxLength(max_length)
        # 设置文本框显示宽度（可选）
        if display_width is not None:
            self.line_text.setFixedWidth(display_width)
        # 设置文本框显示提示文本（可选）
        if text is not None:
            self.line_text.setPlaceholderText(text)
        self.layout = QHBoxLayout(self)
        self.layout.addWidget(self.label) 
        self.layout.addWidget(self.line_text)
        # self.layout.setContentsMargins(0, 0, 0, 0)  # 移除所有外边距
        # self.layout.setSpacing(0)                   # 控件间距设为0
        # 添加控件时禁止拉伸（关键）
        # self.layout.addWidget(self.label, stretch=0) 
        # self.layout.addWidget(self.line_text, stretch=0)
        # 添加一个不可见的弹簧吸收多余空间（防止意外拉伸）
        self.layout.addSpacerItem(QSpacerItem(0, 0, QSizePolicy.Expanding, QSizePolicy.Minimum))

    def clear(self):
        self.line_text.clear()

class StartButtonWidget(QWidget):
    def __init__(self, start_exce, title: str = None):
        super().__init__()
        if not callable(start_exce):
            raise ValueError("start_exce must be a callable object")
            
        if title == None:
            title = "Progress bar"
        self.label = QLabel(f"{title}:")
        self.button_start = QPushButton('start')
        self.progress_bar = QProgressBar()
        self.progress_bar.setValue(0)
        self.layout = QHBoxLayout(self)
        self.layout.addWidget(self.label)
        self.layout.addWidget(self.progress_bar)
        self.layout.addWidget(self.button_start)
        
        self.button_start.clicked.connect(start_exce)

    def ProgressBar(self, check_process): 
        self.progress_bar.setValue(check_process)
        QCoreApplication.processEvents()

    def clear(self):
        self.progress_bar.setValue(0)

class TableWindow(QWidget):
    def __init__(self, parent=None):
        super().__init__(parent)
        self.layout = QVBoxLayout(self)
        # 创建表格
        self.table = QTableWidget()
        # self.table.setSizePolicy(QSizePolicy.Expanding, QSizePolicy.Expanding)  # 水平和垂直均扩展
        self.table.horizontalHeader().setSectionResizeMode(QHeaderView.Stretch)  # 所有列均匀拉伸
        self.table.hide()
        self.layout.addWidget(self.table)
        # 添加导出按钮
        self.export_button = QPushButton("导出")
        self.export_button.hide()
        self.export_button.clicked.connect(self.export_table)
        self.layout.addWidget(self.export_button, alignment=Qt.AlignRight)

    def clear_table(self):
        """清空表格内容"""
        # 方法1：推荐的标准方式
        self.table.clearContents()
        self.table.setRowCount(0)
        self.table.hide()
        self.export_button.hide()

    def show_table(self):
        """显示表格和导出按钮"""
        # 自动调整列宽和行高以适应内容
        self.table.resizeColumnsToContents()
        self.table.resizeRowsToContents()
        self.table.show()
        self.export_button.show()
    
    def set_header(self, data):
        self.table.setHorizontalHeaderLabels(data)
        
    def set_data(self, data):
        """设置初始数据"""
        self.table.setRowCount(len(data))
        self.table.setColumnCount(len(data[0]))
        for row_idx, row_data in enumerate(data):
            for col_idx, item in enumerate(row_data):
                self.table.setItem(row_idx, col_idx, QTableWidgetItem(str(item)))
        
    def add_row(self, row_data):
        """动态添加一行数据"""
        row_position = self.table.rowCount()
        self.table.insertRow(row_position)
        for col_idx, item in enumerate(row_data):
            self.table.setItem(row_position, col_idx, QTableWidgetItem(str(item)))
        
    def add_column(self, header, column_data):
        """动态添加一列数据"""
        col_position = self.table.columnCount()
        self.table.insertColumn(col_position)
        self.table.setHorizontalHeaderItem(col_position, QTableWidgetItem(header))
        for row_idx, item in enumerate(column_data):
            self.table.setItem(row_idx, col_position, QTableWidgetItem(str(item)))

    def adjust_columns(self):
        """根据表格宽度自动调整每一列的宽度"""
        total_width = self.table.viewport().size().width()
        num_columns = self.table.columnCount()
        if num_columns == 0:
            return
        # 假设每列之间有 10 像素的间距
        FIXED_SPACING = 10
        total_spacing = FIXED_SPACING * (num_columns - 1)
        available_width = total_width - total_spacing
        column_width = available_width / num_columns if num_columns > 0 else 0
        for col in range(num_columns):
            self.table.setColumnWidth(col, column_width)
    
    def resizeEvent(self, event):
        """重写resizeEvent以在窗口大小变化时调整列宽"""
        super().resizeEvent(event)
        if self.table.isVisible():
            self.adjust_columns()
        
    def export_table(self):
        # 打开文件保存对话框
        options = QFileDialog.Options()
        file_name, _ = QFileDialog.getSaveFileName(
            self,
            "保存文件",
            "",
            "CSV 文件 (*.csv);;所有文件 (*)",
            options=options
        )
        if file_name:
            try:
                # 添加列名
                column_names = [self.table.horizontalHeaderItem(i).text() for i in range(self.table.columnCount())]
                with open(file_name, 'w', newline='', encoding='utf-8-sig') as file:
                    writer = csv.writer(file)
                    writer.writerow(column_names)  # 写入列名
                    for row in range(self.table.rowCount()):
                        row_data = []
                        for col in range(self.table.columnCount()):
                            item = self.table.item(row, col)
                            row_data.append(item.text() if item else "")
                        writer.writerow(row_data)  # 写入行数据
                QMessageBox.information(self, "成功", f"数据已成功导出到 {file_name}")
            except Exception as e:
                QMessageBox.critical(self, "错误", f"导出失败: {e}")

class ExcelReader:
    def __init__(self, file_path: str):
        """
        初始化ExcelReader，加载Excel文件。
        :param file_path: Excel文件的路径
        """
        try:
            self.workbook = load_workbook(filename=file_path)
            print(f"成功加载文件: {file_path}")
        except Exception as e:
            print(f"加载文件失败: {e}")
            self.workbook = None
            raise

    def get_sheet_used_range(self, sheet_name: str) -> dict:
        """
        获取工作表的使用范围信息
        
        :param sheet_name: 要检查的工作表名称
        :return: 包含使用范围信息的字典，格式为:
                {
                    'dimensions': 'A1:D10' (字符串格式的范围),
                    'max_row': 10 (最大行号),
                    'max_column': 4 (最大列号),
                    'start_cell': 'A1' (起始单元格),
                    'end_cell': 'D10' (结束单元格)
                }
                如果工作表不存在或为空，返回None
        """
        sheet = self.get_sheet(sheet_name)
        if not sheet:
            return None
        
        try:
            # 检查工作表是否为空
            if sheet.dimensions == 'A1:A1' and sheet['A1'].value is None:
                print(f"工作表 '{sheet_name}' 为空")
                return None
            
            # 解析dimensions获取起始和结束单元格
            dimensions = sheet.dimensions.split(':')
            start_cell = dimensions[0]
            end_cell = dimensions[1] if len(dimensions) > 1 else dimensions[0]
            
            return {
                'dimensions': sheet.dimensions,
                'max_row': sheet.max_row,
                'max_column': sheet.max_column,
                'start_cell': start_cell,
                'end_cell': end_cell
            }
        except Exception as e:
            print(f"获取工作表 '{sheet_name}' 使用范围时出错: {e}")
            raise

    def get_all_sheets(self) -> List[str]:
        """
        获取工作簿中所有工作表的名称列表
        :return: 工作表名称列表
        """
        if not self.workbook:
            return []
        return self.workbook.sheetnames

    def get_sheet(self, sheet_name: str):
        """
        根据工作表名称获取工作表对象
        
        :param sheet_name: 要获取的工作表名称
        :return: 工作表对象，如果未找到则返回None
        """
        if not self.workbook:
            return None
        
        try:
            # 检查工作表是否存在
            if sheet_name in self.workbook.sheetnames:
                return self.workbook[sheet_name]
            else:
                print(f"警告: 未找到工作表 '{sheet_name}'")
                return None
        except Exception as e:
            print(f"获取工作表 '{sheet_name}' 时出错: {e}")
            raise

    def read_all_data(self) -> dict:
        """
        读取整个Excel文件的数据。
        :return: 字典，键为工作表名称，值为工作表数据
        """
        if not self.workbook:
            raise ValueError("工作簿未正确加载。")
        
        all_data = {}
        for sheet in self.workbook.sheetnames:
            if self.workbook[sheet].sheet_state == 'visible':
                all_data[sheet] = self.read_sheet_data(sheet)
        return all_data

    def read_sheet_data(self, sheet_name: str) -> List[List[Any]]:
        """
        读取指定工作表的所有数据。
        :param sheet_name: 工作表名称
        :return: 二维列表，表示工作表的数据
        """
        if not self.workbook:
            raise ValueError("工作簿未正确加载。")
        
        try:
            sheet = self.workbook[sheet_name]
            data = []
            for row in sheet.iter_rows(values_only=True):
                data.append(list(row))
            return data
        except Exception as e:
            print(f"读取工作表 '{sheet_name}' 失败: {e}")
            raise

    def read_column_data(self, sheet_name: str, column: str) -> List[Any]:
        """
        读取指定工作表中某一列的数据。
        :param sheet_name: 工作表名称
        :param column: 列标识（如 'A', 'B', ...）
        :return: 列表，包含指定列的所有数据
        """
        if not self.workbook:
            raise ValueError("工作簿未正确加载。")
        
        try:
            sheet = self.workbook[sheet_name]
            column_data = []
            for cell in sheet[column]:
                column_data.append(cell.value)
            return column_data
        except Exception as e:
            print(f"读取工作表 '{sheet_name}' 列 '{column}' 失败: {e}")
            raise

    def read_row(self, sheet_name, row_number):
        """
        读取指定Sheet页中的某一行数据。
        :param sheet_name: Sheet的名称
        :param row_number: 行号（从1开始）
        :return: 包含该行数据的列表或None
        """
        if not self.workbook:
            raise ValueError("工作簿未正确加载。")

        sheet = self.get_sheet(sheet_name)
        if not sheet:
            raise ValueError(f"未找到[sheet_name]sheet页。")
        try:
            row_data = []
            for cell in sheet[row_number]:
                row_data.append(cell.value)
            print(f"读取到第{row_number}行的数据: {row_data}")
            return row_data
        except IndexError:
            print(f"行号{row_number}超出范围。")
            raise
        except Exception as e:
            print(f"读取行数据时发生错误: {e}")
            raise

    def read_cell(self, sheet_name, row, column):
        """
        读取指定工作表中的某个单元格。
        :param sheet: 工作表对象
        :param row: 行号（1-based）
        :param column: 列号（A-based）
        :return: 单元格的值或None
        """
        if not self.workbook:
            raise ValueError("工作簿未正确加载。")
        sheet = self.get_sheet(sheet_name)
        if not sheet:
            raise ValueError(f"未找到[sheet_name]sheet页。")
        try:
            cell_value = sheet.cell(row=row, column=column_index_from_string(column)).value
            print(f"读取单元格({row}, {column})的值: {cell_value}")
            return cell_value
        except Exception as e:
            print(f"读取单元格时发生错误: {e}")
            raise

def is_valid_date(date_str):
    try:
        datetime.strptime(date_str, '%Y/%m/%d')
        return True
    except ValueError:
        return False

def are_dates_equal(date_str1, format1, date_str2, format2):
    """
    判断两个日期字符串是否表示相同的日期。
    参数：
    date_str1 (str): 第一个日期字符串，格式如 "YYYY-MM-DD HH:MM:SS"
    format1: 第一个日期格式"%Y-%m-%d %H:%M:%S"
    date_str2 (str): 第二个日期字符串，格式如 "YYYY/M/D"
    format1: 第二个日期格式"%Y/%m/%d"
    返回：
    bool: 如果两个日期相同，返回True；否则返回False。
    """
    # 定义对应的日期格式
    # format1 = "%Y-%m-%d %H:%M:%S"
    # format2 = "%Y/%m/%d"
    try:
        # 解析第一个日期字符串
        date1 = datetime.strptime(date_str1, format1)
        # 解析第二个日期字符串
        date2 = datetime.strptime(date_str2, format2)
        # 比较日期部分（年、月、日）
        return date1.date() == date2.date()
    except ValueError as e:
        print(f"日期格式错误: {e}")
        return False

class ExcelHandler:
    def __init__(self):
        self.excel_app = None
        self.workbook = None
        self._is_open = False

    def open_workbook(self, file_path: str, visible: bool = False, read_only: bool = False) -> bool:
        """打开Excel工作簿"""
        try:
            pythoncom.CoInitialize()
            self.excel_app = win32.dynamic.Dispatch('Excel.Application')
            self.excel_app.Visible = visible
            self.excel_app.ScreenUpdating = False  # 关闭屏幕更新
            self.workbook = self.excel_app.Workbooks.Open(file_path, ReadOnly=read_only)
            self._is_open = True
            return True
        except Exception as e:
            print(f"打开工作簿失败: {e}")
            self._cleanup()
            return False

    def sheet_exists(self, sheet_name: str) -> bool:
        """检查工作表是否存在"""
        if not self._is_open:
            raise RuntimeError("工作簿未打开")
        try:
            return any(sheet.Name == sheet_name for sheet in self.workbook.Worksheets)
        except Exception as e:
            print(f"检查工作表存在失败: {e}")
            return False

    def get_sheet(self, sheet_name: str) -> Optional[win32.CDispatch]:
        """获取指定工作表对象"""
        if not self.sheet_exists(sheet_name):
            return None
        try:
            return self.workbook.Worksheets(sheet_name)
        except Exception as e:
            print(f"获取工作表失败: {e}")
            return None
    
    def get_all_sheet_names(self) -> List[str]:
        """获取所有工作表名称列表"""
        if not self._is_open:
            raise RuntimeError("工作簿未打开")
        try:
            # return [sheet.Name for sheet in self.workbook.Worksheets]
            return [
                    sheet.Name 
                    for sheet in self.workbook.Worksheets 
                    if sheet.Visible == xl.xlSheetVisible
                ]
        except Exception as e:
            print(f"获取工作表列表失败: {e}")
            return []

    def save(self, new_path: Optional[str] = None) -> bool:
        """保存工作簿"""
        if not self._is_open:
            raise RuntimeError("工作簿未打开")
        try:
            if new_path:
                self.workbook.SaveAs(new_path)
            else:
                self.workbook.Save()
            return True
        except Exception as e:
            print(f"保存工作簿失败: {e}")
            return False
        finally:
            self.excel_app.ScreenUpdating = True  # 确保恢复更新

    def close(self, save_changes: bool = False) -> None:
        """关闭工作簿"""
        if self._is_open:
            try:
                self.excel_app.ScreenUpdating = True  # 恢复屏幕更新
                self.workbook.Close(save_changes)
            except Exception as e:
                print(f"关闭工作簿失败: {e}")
            finally:
                self._cleanup()

    def _cleanup(self) -> None:
        """清理资源"""
        if hasattr(self, 'excel_app') and self.excel_app:
            self.excel_app.Quit()
        self.excel_app = None
        self.workbook = None
        self._is_open = False
        pythoncom.CoUninitialize()

    def __enter__(self):
        """支持with上下文管理"""
        return self

    def __exit__(self, exc_type, exc_val, exc_tb):
        """退出上下文时自动关闭"""
        self.close(save_changes=False)

class ExcelInspector:
    def __init__(self, file_path: str, visible: bool = False):
        """
        初始化Excel检查器
        :param file_path: Excel文件路径
        :param visible: 是否显示Excel应用程序窗口
        """
        self.file_path = file_path
        self.app = xw.App(visible=visible)
        try:
            self.wb = self.app.books.open(file_path)
            print(f"成功加载文件: {file_path}")
        except Exception as e:
            self.app.quit()
            raise Exception(f"文件加载失败: {e}")

    def get_sheet_used_range(self, sheet_name: str) -> Optional[Dict]:
        """
        获取单个sheet的使用范围信息
        :param sheet_name: 工作表名称
        :return: 包含范围信息的字典，格式如下：
                {
                    'sheet_name': str,
                    'used_range': str (如"A1:D10"),
                    'row_count': int,
                    'column_count': int,
                    'first_cell': str,
                    'last_cell': str,
                    'has_data': bool
                }
        """
        try:
            sheet = self.wb.sheets[sheet_name]
            used_range = sheet.used_range
            
            # 获取范围信息
            address = used_range.address.replace('$', '')
            first_cell = used_range[0].address.replace('$', '')
            last_cell = used_range[-1].address.replace('$', '')
            
            return {
                'sheet_name': sheet_name,
                'used_range': address,
                'row_count': used_range.rows.count,
                'column_count': used_range.columns.count,
                'first_cell': first_cell,
                'last_cell': last_cell,
                'has_data': self._check_range_has_data(used_range)
            }
        except Exception as e:
            print(f"获取工作表 '{sheet_name}' 范围时出错: {e}")
            return None

    def _check_range_has_data(self, range_obj) -> bool:
        """检查范围是否包含真实数据"""
        try:
            # 快速检查前10个单元格
            sample_cells = range_obj[:10]
            for cell in sample_cells:
                if cell.value not in [None, "", " "]:
                    return True
            return False
        except:
            return False

    def get_all_sheets_info(self) -> List[Dict]:
        """获取所有工作表的使用范围信息"""
        results = {}
        for sheet in self.wb.sheets:
            info = self.get_sheet_used_range(sheet.name)
            if info:
                results[sheet.name] = info
        return results

    def close(self):
        """关闭工作簿和Excel应用"""
        try:
            self.wb.close()
            self.app.quit()
            print("资源已释放")
        except Exception as e:
            print(f"关闭资源时出错: {e}")

    def __enter__(self):
        return self

    def __exit__(self, exc_type, exc_val, exc_tb):
        self.close()



def process_group_shape(group_shape, sheet, DRBFM_path, sheet_data, sample_data):
    """
    处理组合图形及其子图形
    """
    try:
        # 获取组合中的子图形数量
        group_items = group_shape.GroupItems
        
        # 遍历组合中的每个子图形
        for j in range(1, group_items.Count + 1):
            sub_shape = group_items(j)
            
            # 如果子图形本身也是组合图形，递归处理
            if sub_shape.Type == 6:
                process_group_shape(sub_shape, sheet, DRBFM_path, sheet_data, sample_data)
            else:
                process_single_shape(sub_shape, sheet, DRBFM_path, sheet_data, sample_data)
                
    except Exception as e:
        print(f"处理组合图形时出错: {e}")
        sample_data.append([
            DRBFM_path, 
            sheet.Name, 
            f"组合图形:{group_shape.Name}", 
            "-", 
            f"处理组合图形出错: {str(e)}"
        ])

def process_single_shape(shape, sheet, DRBFM_path, sheet_data, sample_data):
    if shape.Type in (17, 1):
        address = f"{shape.TopLeftCell.Address}-{shape.BottomRightCell.Address}".replace("$","")
        try:
            # 更安全的文本获取方式
            if hasattr(shape, 'TextFrame2'):
                text = shape.TextFrame2.TextRange.Text
                if not text or text.isspace():
                    return
        except AttributeError:
            print(f"警告: [{sheet.Name}]图形 [{address}] 的 TextFrame2 不支持 TextRange, 跳过处理")
            # sample_data.append([DRBFM_path, sheet.Name, address, "-", "TextFrame2 不支持 TextRange, 跳过处理"])
            return
        except pythoncom.com_error as e:
            if e.excepinfo[5] == -2147024809:  # 边界错误代码
                print(f"警告: [{sheet.Name}][{address}]","连接器/线条，跳过处理")
                # sample_data.append([DRBFM_path, sheet.Name, address, "-", "连接器/线条，跳过处理"])
            else:
                sample_data.append([DRBFM_path, sheet.Name, address, "-", "需要修复状态, 跳过处理"])
            return
        
        setting_flag = True
        rgb_list = ""
        for row_list in sheet_data:
            if str(row_list[0]).strip() == str(sheet.Name).strip():
                if str(row_list[1]).strip() == str(text).strip():
                    rgb_value = str(row_list[2]).strip()
                    setting_flag = False
                    print(f"Processing shape: [{address}], Text: [{text}], rules text: [{str(row_list[1]).strip()}]")
                    break

        if setting_flag == True:
            original_left = shape.Left
            original_top = shape.Top
            # original_width = shape.Width
            # original_height = shape.Height
            # rotation = shape.Rotation  # 获取旋转角度
            shape.TextFrame2.AutoSize = 1
            shape.Left = original_left
            shape.Top = original_top
            # new_width = shape.Width - original_width
            # new_height = shape.Height - original_height
            # # 自动调整发生后，恢复左上角位置
            # if new_width > 0:
            #     shape.Left = original_left - (new_width / 2)
            # else:
            #     shape.Left = original_left
            # if new_height > 0:
            #     shape.Top = original_top + (new_height / 2)
            # else:
            #     shape.Top = original_top
            sample_data.append([DRBFM_path, sheet.Name, address, text, "根据文本调整图形大小设置完成"])
            return
        else:
            if rgb_value == "-":
                sample_data.append([DRBFM_path, sheet.Name, address, text, "根据文本匹配图形文本框成功, 跳过处理"])
                return
            else:
                rgb_list = rgb_value.split("/")
                rgb_color = (int(rgb_list[2]) << 16) | (int(rgb_list[1]) << 8) | int(rgb_list[0])
        
        try:
            if hasattr(shape, "Fill"):
                if shape.Fill.Visible == -1:  # 填充可见
                    fill_color = shape.Fill.ForeColor.RGB
                    if rgb_color == fill_color:
                        print(f"Processing shape: [{address}], Text: [{text}], rules text: [{str(row_list[1]).strip()}, Fill Color: [{fill_color}], rgb Color: [{rgb_color}]")
                        sample_data.append([DRBFM_path, sheet.Name, address, f"[{text}][{fill_color}]", "根据文本和填充色匹配图形文本框成功, 跳过处理"])
                        return

            shape.TextFrame2.AutoSize = 1
            sample_data.append([DRBFM_path, sheet.Name, address, f"[{text}][{fill_color}]", "根据文本和填充色调整图形大小设置完成"])
        except Exception as e:
            sample_data.append([DRBFM_path, sheet.Name, address, text, f"填充属性时出错: {e}, 跳过处理"])


def confirm_file_status(self):
    reply = QMessageBox.question(
        self,  # 父窗口
        "确认操作",  # 标题
        "运行工具时，Excel 进程可能会被强制终止，请确认所有文件是否已保存并关闭?",  # 消息内容
        QMessageBox.Yes | QMessageBox.No,  # 按钮类型
        QMessageBox.No  # 默认选中按钮
    )
    
    if reply == QMessageBox.Yes:
        return True
    else:
        return False

def get_true_last_row_and_col(sheet):
    last_row = sheet.UsedRange.Rows.Count
    last_col = sheet.UsedRange.Columns.Count
    # print(f"last_row={last_row} last_col={last_col}")
    try:
        cell_last_row = sheet.Cells.Find("*", SearchOrder=win32.constants.xlByRows, SearchDirection=win32.constants.xlPrevious).Row
        cell_last_col = sheet.Cells.Find("*", SearchOrder=win32.constants.xlByColumns, SearchDirection=win32.constants.xlPrevious).Column
    except AttributeError:  # 全空表
        cell_last_row = 1
        cell_last_col = 1
    # print(f"cell_last_row={cell_last_row} cell_last_col={cell_last_col}")
    if last_row != cell_last_row or last_col != cell_last_col:
        if last_row > 10000:
            last_row = cell_last_row + 100
        if last_col > 500:
            last_col = cell_last_col + 50

        row_last_row = []
        row_last_col = []
        row_last_row.append(cell_last_row)
        row_last_col.append(cell_last_col)
        
        for shape in sheet.Shapes:
            if shape.Visible == -1:
                row_last_row.append(shape.BottomRightCell.Row)
                row_last_col.append(shape.BottomRightCell.Column)
        
        if last_row != cell_last_row:
            # print(f"A{cell_last_row}:{get_column_letter(last_col)}{last_row}")
            # 获取范围内的所有合并单元格
            merged_cells = sheet.Range(f"A{cell_last_row}:{get_column_letter(last_col)}{last_row}")
            for cell in merged_cells:
                if cell.MergeCells:
                    mergecell = cell.MergeArea
                    if mergecell.Rows.Count < 100:
                        row_last_row.append(mergecell.Row + mergecell.Rows.Count -1)
        if row_last_row:
            cell_last_row = max(row_last_row)
        last_row = cell_last_row

        if last_col != cell_last_col:
            # print(f"{get_column_letter(cell_last_col)}1:{get_column_letter(last_col)}{last_row}")
            # 获取范围内的所有合并单元格
            merged_cells = sheet.Range(f"{get_column_letter(cell_last_col)}1:{get_column_letter(last_col)}{last_row}")
            for cell in merged_cells:
                if cell.MergeCells:
                    mergecell = cell.MergeArea
                    if mergecell.Columns.Count < 100:
                        row_last_col.append(mergecell.Column + mergecell.Columns.Count -1)
        
        if row_last_col:
            cell_last_col = max(row_last_col)
        last_col = cell_last_col

    return last_row, last_col

def setting_group_shape(group_shape, sheet, DRBFM_path, row_list, sample_data):
    """
    处理组合图形及其子图形
    """
    try:
        # 获取组合中的子图形数量
        group_items = group_shape.GroupItems
        address = f"{group_shape.TopLeftCell.Address}-{group_shape.BottomRightCell.Address}".replace("$","")
        # 遍历组合中的每个子图形
        for j in range(1, group_items.Count + 1):
            sub_shape = group_items(j)
            
            # 如果子图形本身也是组合图形，递归处理
            if sub_shape.Type == 6:
                setting_group_shape(sub_shape, sheet, DRBFM_path, row_list, sample_data)
            else:
                setting_single_shape(sub_shape, sheet, DRBFM_path, row_list, sample_data)
                
    except Exception as e:
        print(f"处理组合图形时出错: {e}")
        sample_data.append([
            DRBFM_path, 
            sheet.Name, 
            address, 
            "-", 
            f"处理组合图形出错: {str(e)}"
        ])

def setting_single_shape(shape, sheet, DRBFM_path, row_list, sample_data):
    # if shape.Type in (17, 9, 1, 2, 4, 13):
    if shape.TopLeftCell.Row in row_list:
        address = f"{shape.TopLeftCell.Address}-{shape.BottomRightCell.Address}".replace("$","")
        if shape.Line.ForeColor.RGB == 0x0000FF:  # 红色
            shape.Line.ForeColor.RGB = 0x000000  # 黑色
            sample_data.append([DRBFM_path, sheet.Name, address, "-", "设置边框颜色为黑色"])
        try:
            # 更安全的文本获取方式
            if hasattr(shape, 'TextFrame2'):
                text_range = shape.TextFrame2.TextRange
                text = text_range.Text
                if not text or text.isspace():
                    return
                else:
                    try:
                        has_red_text = False
                        if (hasattr(text_range.Font.Fill, 'ForeColor') and 
                            hasattr(text_range.Font.Fill.ForeColor, 'RGB') and 
                            text_range.Font.Fill.ForeColor.RGB == 255):  # 红色
                                has_red_text = True
                        else:
                            # 检查每个字符的颜色
                            if hasattr(text_range, "GetCharacters"):
                                for i in range(1, len(text) + 1):
                                    char_range = text_range.GetCharacters(i, 1)
                                    if (hasattr(char_range.Font.Fill, 'ForeColor') and 
                                        hasattr(char_range.Font.Fill.ForeColor, 'RGB') and
                                        char_range.Font.Fill.ForeColor.RGB == 255):  # 红色
                                        has_red_text = True
                                        break
                    except:
                        print(f"警告: [{sheet.Name}]图形 [{address}] 的字符颜色检查失败, 跳过处理文本颜色设置")
                        return
                    if has_red_text:
                        text_range.Font.Fill.ForeColor.RGB = 0x000000
                        sample_data.append([DRBFM_path, sheet.Name, address, "-", "设置文本颜色为黑色"])
        except AttributeError:
            print(f"警告: [{sheet.Name}]图形 [{address}] 的 TextFrame2 不支持 TextRange, 跳过处理文本颜色设置")
            # sample_data.append([DRBFM_path, sheet.Name, address, "-", "TextFrame2 不支持 TextRange, 跳过处理"])
            return
        except pythoncom.com_error as e:
            if e.excepinfo[5] == -2147024809:  # 边界错误代码
                print(f"警告: [{sheet.Name}][{address}]","连接器/线条，跳过处理文本颜色设置")
                # sample_data.append([DRBFM_path, sheet.Name, address, "-", "连接器/线条，跳过处理"])
            else:
                sample_data.append([DRBFM_path, sheet.Name, address, "-", "需要修复状态, 跳过处理文本颜色设置"])
            return
    # else:
    #     sample_data.append([DRBFM_path, sheet.Name, address, shape.Type, "shape.Type类型未处理处理,如需处理请联系工具担当"])


def check_and_set_group_shape(group_shape, sheet, DRBFM_path, target_row, sample_data):
    """
    处理组合图形及其子图形
    """
    try:
        # 获取组合中的子图形数量
        group_items = group_shape.GroupItems
        address = f"{group_shape.TopLeftCell.Address}-{group_shape.BottomRightCell.Address}".replace("$","")
        # 遍历组合中的每个子图形
        for j in range(1, group_items.Count + 1):
            sub_shape = group_items(j)
            # 如果子图形本身也是组合图形，递归处理
            if sub_shape.Type == 6:
                check_and_set_group_shape(sub_shape, sheet, DRBFM_path, target_row, sample_data)
            else:
                check_and_set_single_shape(sub_shape, sheet, DRBFM_path, target_row, sample_data)
                
    except Exception as e:
        print(f"处理组合图形时出错: {e}")
        sample_data.append([
            DRBFM_path, 
            sheet.Name, 
            address, 
            "-", 
            f"处理组合图形出错: {str(e)}"
        ])

def check_and_set_single_shape(shape, sheet, DRBFM_path, target_row, sample_data):
    # if shape.Type in (17, 9, 1, 2, 4, 13):
    # 红色边框
    address = f"{shape.TopLeftCell.Address}-{shape.BottomRightCell.Address}".replace("$","")
    start_row = shape.TopLeftCell.Row
    end_row = shape.BottomRightCell.Row
    row_flag = False
    if shape.Line.ForeColor.RGB == 0x0000FF:
        row_flag = True
    try:
        # 更安全的文本获取方式
        if hasattr(shape, 'TextFrame2'):
            text = shape.TextFrame2.TextRange.Text
            if not text or text.isspace():
                return
            else:
                if shape.TextFrame2.TextRange.Font.Fill.ForeColor.RGB == 0x0000FF:
                    if row_flag == False:
                        sample_data.append([DRBFM_path, sheet.Name, address, "-", "边框未标红，请检查"])
                    row_flag = True
                else:
                    if row_flag:
                        sample_data.append([DRBFM_path, sheet.Name, address, "-", "文本颜色不是红色，请检查"])
    except AttributeError:
        print(f"警告: [{sheet.Name}]图形 [{address}] 的 TextFrame2 不支持 TextRange, 跳过处理文本颜色检查")
        # sample_data.append([DRBFM_path, sheet.Name, address, "-", "TextFrame2 不支持 TextRange, 跳过处理"])
        return
    except pythoncom.com_error as e:
        if e.excepinfo[5] == -2147024809:  # 边界错误代码
            print(f"警告: [{sheet.Name}][{address}]","连接器/线条，跳过处理文本颜色检查")
            # sample_data.append([DRBFM_path, sheet.Name, address, "-", "连接器/线条，跳过处理"])
        else:
            sample_data.append([DRBFM_path, sheet.Name, address, "-", "需要修复状态, 跳过处理文本颜色检查"])
        return

    if row_flag:
        for row in range(start_row, end_row +1, 1):
            target_row.append(row)
    # else:
    #     sample_data.append([DRBFM_path, sheet.Name, address, shape.Type, "shape.Type类型未处理处理,如需处理请联系工具担当"])


def get_group_shape(group_shape, shape_dict):
    """
    处理组合图形及其子图形
    """
    try:
        # 获取组合中的子图形数量
        group_items = group_shape.GroupItems
        address = f"{group_shape.TopLeftCell.Address}-{group_shape.BottomRightCell.Address}".replace("$","")
        # 遍历组合中的每个子图形
        for j in range(1, group_items.Count + 1):
            sub_shape = group_items(j)
            
            # 如果子图形本身也是组合图形，递归处理
            if sub_shape.Type == 6:
                get_group_shape(sub_shape, shape_dict)
            else:
                get_single_shape(sub_shape, shape_dict)
                
    except Exception as e:
        print(f"处理组合图形{group_shape.Name}:{address}时出错: {e}")

def get_single_shape(shape, shape_dict):
    address = f"{shape.TopLeftCell.Address}-{shape.BottomRightCell.Address}".replace("$","")
    if shape.Type == 13:
        # 将图片复制到剪贴板
        shape.CopyPicture()
        # 确保剪贴板稳定
        pythoncom.CoInitialize()
        try:
            img = ImageGrab.grabclipboard()
            if img is not None:
                from io import BytesIO
                buffer = BytesIO()
                img.save(buffer, format="PNG")
                img_data = buffer.getvalue()
                # 计算图片的MD5哈希
                md5_hash = hashlib.md5(img_data).hexdigest()
                shape_dict[shape.Name] = { "Value" : md5_hash , "address" : address}
        except Exception as e:
            print(f"警告: [{shape.Name}][{address}]处理图片时出错: {e}")
        finally:
            pythoncom.CoUninitialize()
        return

    try:
        # 更安全的文本获取方式
        if hasattr(shape, 'TextFrame2'):
            text_range = shape.TextFrame2.TextRange
            font = text_range.Font
            # text = text_range.Text
            # if not text or text.isspace():
            #     return
            # else:
            try:
                shape_dict[shape.Name] = {
                            "Value" : str(text_range.Text).strip() ,
                            "fontName" : font.Name ,
                            "fontSize" : font.Size ,
                            "fontColor" : font.Fill.ForeColor.RGB ,
                            "fontStrikethrough" : font.Strikethrough ,
                            "InteriorColor" : shape.Fill.ForeColor.RGB ,
                            "address" : address
                            }
            except:
                print(f"警告: [{shape.Name}]图形 [{address}] 的字符颜色检查失败, 跳过处理文本")
                return
    except AttributeError:
        print(f"警告: [{shape.Name}]图形 [{address}] 的 TextFrame2 不支持 TextRange, 跳过处理文本")
        return
    except pythoncom.com_error as e:
        if e.excepinfo[5] == -2147024809:  # 边界错误代码
            print(f"警告: [{shape.Name}][{address}]连接器/线条，跳过处理文本")
        else:
            print(f"警告: [{shape.Name}][{address}]需要修复状态, 跳过处理文本")
        return

